package com.rg1906.dao.Impl;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.rg1906.entity.StudentManage;
import com.rg1906.utils.DataSourceC3P0ThreadLocal;

public class StudentManageDaoImpl {
	DataSource dataSource = DataSourceC3P0ThreadLocal.getDataSource();
	QueryRunner qr = new QueryRunner(dataSource);
	public List<StudentManage> QueryStudent(int pageNumberInt, int pageSizeInt, String likeName, String studentNum, String gradeName, String gradeClazz) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("t.id,");
		sb.append("t.realname,");
		sb.append("t.studentNum,");
		sb.append("CONCAT( grade.gradename, class.gradename ) gradeClass,");
		sb.append("t.phone,");
		sb.append("t.address,");
		sb.append("t.sex,");
		sb.append("t.age ");
		sb.append("FROM ");
		sb.append("t_student t ");
		sb.append("LEFT JOIN t_grade grade ON t.gid=grade.id ");
		sb.append("LEFT JOIN t_grade class ON t.cid = class.id ");
			sb.append("where 1=1 ");
		if(likeName!=null&&!"".equals(likeName)) {
			sb.append("and t.realname like '%"+likeName+"%'"+" ");
		}
		if(studentNum!=null&&!"".equals(studentNum)) {
			sb.append("and t.studentNum like '%"+studentNum+"%'"+" ");
		}
		if(gradeName!=null&&!"".equals(gradeName)) {
			sb.append("and grade.gradename='"+gradeName+"' ");
		}
		if(gradeClazz!=null&&!"".equals(gradeClazz)) {
			sb.append("and class.gradename='"+gradeClazz+"' ");
		}
		sb.append("limit ");
		sb.append("?,?");
		String sql = sb.toString();
		Object[] par= {pageNumberInt*pageSizeInt,pageSizeInt};
		List<StudentManage> list = qr.query(sql, new BeanListHandler<>(StudentManage.class),par);
		return list;
		
	}
	public Long getTotal(String likeName, String studentNum, String gradeClazz, String gradeName) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("count(0) ");
		sb.append("FROM ");
		sb.append("t_student t ");
		sb.append("LEFT JOIN t_grade grade ON t.gid=grade.id ");
		sb.append("LEFT JOIN t_grade class ON t.cid = class.id ");
		sb.append("where 1=1 ");
		if(likeName!=null&&!"".equals(likeName)) {
			sb.append("and t.realname like '%"+likeName+"%'"+" ");
		}
		if(studentNum!=null&&!"".equals(studentNum)) {
			sb.append("and t.studentNum like '%"+studentNum+"%'"+" ");
		}
		if(gradeName!=null&&!"".equals(gradeName)) {
			sb.append("and grade.gradename='"+gradeName+"' ");
		}
		if(gradeClazz!=null&&!"".equals(gradeClazz)) {
			sb.append("and class.gradename='"+gradeClazz+"' ");
		}
		String sql=sb.toString();
		Long total = qr.query(sql, new ScalarHandler<>());
		return total;
	}
}
